﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using ExportFile;
using Maticsoft.Model;
using System.Windows.Forms;
using DevComponents.DotNetBar.Controls;
using Aspose.Cells;
using Maticsoft.Common;
using System.Collections;

namespace Maticsoft.BLL
{
    public class frmMain
    {
        /// <summary>
        /// 获取预览数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetYLDB(String strName)
        {
            try
            {
                DataTable dt = DbHelperSQL.Query(string.Format("select top 25 * from {0}", strName)).Tables[0];
                return dt;
            }
            catch
            {
                MessageBox.Show("数据表不存在", "提示");
                return null;
            }
        }

        /// <summary>
        /// 导出报表
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="str"></param>
        public static void Export(Object obj, String str)
        {
            try
            {
                if (Maticsoft.Model.Columns.ary_col.Count <= 0) GetAry(str);
                else GetDt(str);
                export ex = new export();
                ex.Ary = Maticsoft.Model.Columns.ary_col;
                ex.Dt = Maticsoft.Model.Columns.dt;
                ex.Name = Maticsoft.Model.Columns.strName;
                List<export> list = new List<export> { ex };
                if (port.export(list, obj.ToString())) MessageBox.Show("保存完成");
            }
            catch { }
        }

        /// <summary>
        /// 导出报表
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="dgv"></param>
        public static void Export(Object obj, DataGridViewX dgv)
        {
            try
            {
                export ex = new export();
                ex.Ary = Maticsoft.Model.Columns.ary_col;
                ex.Dt = (DataTable)dgv.DataSource;
                ex.Name = DateTime.Now.ToString("yyyy-MM-dd");
                List<export> list = new List<export> { ex };
                if (port.export(list, obj.ToString())) MessageBox.Show("保存完成");
            }
            catch { }
        }

        /// <summary>
        /// 设置数据
        /// </summary>
        /// <param name="str"></param>
        private static void GetDt(String str)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                foreach (string s in Maticsoft.Model.Columns.ary_col)
                {
                    br.Append(s + ",");
                }
                string sql = br.ToString().Substring(0, br.ToString().Length - 1);
                DataTable dt1 = DbHelperSQL.Query(string.Format("select {0} from {1}", sql, str)).Tables[0];
                if (dt1 != null)
                {
                    Maticsoft.Model.Columns.dt = dt1;
                }
                Maticsoft.Model.Columns.strName = str;
            }
            catch { }
        }

        /// <summary>
        /// 设置列名
        /// </summary>
        /// <param name="str"></param>
        private static void GetAry(String str)
        {
            try
            {
                DataTable dt = DbHelperSQL.Query(string.Format("select top 1 * from {0}", str)).Tables[0];
                Maticsoft.Model.Columns.SetColumns(dt);
                DataTable dt1 = DbHelperSQL.Query(string.Format("select * from {0}", str)).Tables[0];
                if (dt1 != null)
                {
                    Maticsoft.Model.Columns.dt = dt1;
                }
                Maticsoft.Model.Columns.strName = str;
            }
            catch { }
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static Object GetSelectDB(String sql)
        {
            try
            {
                DataSet set = DbHelperSQL.Query(sql);
                if (set.Tables.Count <= 0)
                {
                    return "ok";
                }
                return set.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示");
                return null;
            }
        }

        /// <summary>
        /// 获得导入数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataTable GetXls(string fileName)
        {
            try
            {
                Workbook wb = new Workbook();
                wb.Open(fileName);
                Worksheet ws = wb.Worksheets[0];
                DataTable dt = wb.Worksheets[0].Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow + 1, ws.Cells.MaxDataColumn + 1);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    dt.Columns[i].ColumnName = dt.Rows[0][i].ToString();
                }
                dt.Rows.RemoveAt(0);
                return dt;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        public static void SetImport(DataGridViewX dgvImport, string str, ProgressBarX jin)
        {
            jin.Maximum = dgvImport.Rows.Count;
            try
            {
                DataGridViewRow rrr = dgvImport.Rows[0];
                string str111 = GetString("where", rrr);
                for (int ij = 0; ij < dgvImport.Rows.Count; )
                {
                    DataGridViewRow r = dgvImport.Rows[0];
                    StringBuilder sql = new StringBuilder();
                    #region
                    if (Servers.new_db.Count > 0 && Servers.old_db.Count > 0)
                    {
                        if(!str111.ToString().Trim().Equals(""))
                        {
                        string str1 = GetString("where", r);
                        sql.AppendFormat("if(exists( select top 1 * from {0} where 1=1 {1} ))", str, str1);
                        string str4 = GetString("update", r);
                        sql.AppendFormat(" update {0} set {1} where 1=1 {2}", str, str4, str1);
                        sql.Append(" else ");
                        }
                        string str2 = GetString("insert_text", r);
                        string str3 = GetString("insert_value", r);
                        sql.AppendFormat("insert into {0}({1})values({2})", str, str2, str3);
                    }
                    #endregion
                    #region
                    else
                    {
                        string str5 = GetString("value", r);
                        sql.AppendFormat("insert into {0} values({1})", str, str5);
                    }
                    #endregion
                    try
                    {
                        DbHelperSQL.ExecuteSql(sql.ToString());
                    }
                    catch { }
                    jin.Value += 1;
                    dgvImport.Rows.RemoveAt(0);
                    Application.DoEvents();
                }
                jin.Value = 0;
            }
            catch { }
        }

        /// <summary>
        /// 获取查询字符串
        /// </summary>
        public static String GetString(string str, DataGridViewRow r)
        {
            try
            {
                String sql = "";
                StringBuilder b1 = new StringBuilder();
                switch (str)
                {
                    #region 获取查询条件
                    case "where":
                        foreach (DictionaryEntry s in Servers.new_db)
                        {
                            foreach (DictionaryEntry s0 in ((Hashtable)(s.Value)))
                            {
                                if (s0.Value.ToString().Equals("true"))
                                {
                                    foreach (DictionaryEntry s1 in ((Hashtable)Servers.old_db[s.Key]))
                                    {
                                        b1.AppendFormat("and [" + s1.Key.ToString() + "]=" + "'{0}'", r.Cells[s0.Key.ToString()].Value.ToString());
                                    }
                                }
                            }
                        }
                        sql = b1.ToString();
                        break;
                    #endregion
                    #region 获取插入字段
                    case "insert_text":
                        foreach (DictionaryEntry s in Servers.old_db)
                        {
                            foreach (DictionaryEntry s0 in ((Hashtable)(s.Value)))
                            {
                                b1.Append("[" + s0.Key + "],");
                            }
                        }
                        sql = (b1.ToString()).Substring(0, b1.ToString().Length - 1);
                        break;
                    #endregion
                    #region 获取插入值
                    case "insert_value":
                        foreach (DictionaryEntry s in Servers.old_db)
                        {
                            foreach (DictionaryEntry s0 in (Hashtable)Servers.new_db[s.Key])
                            {
                                b1.AppendFormat("'{0}',", r.Cells[s0.Key.ToString()].Value.ToString());
                            }
                        }
                        sql = (b1.ToString()).Substring(0, b1.ToString().Length - 1);
                        break;
                    #endregion
                    #region 获取插入值
                    case "value":
                        foreach (DataGridViewCell re in r.Cells)
                        {
                            b1.AppendFormat("'{0}',", re.Value.ToString());
                        }
                        sql = (b1.ToString()).Substring(0, b1.ToString().Length - 1);
                        break;
                    #endregion
                    #region 获取修改列值
                    case "update":
                        foreach (DictionaryEntry s in Servers.new_db)
                        {
                            foreach (DictionaryEntry s0 in ((Hashtable)(s.Value)))
                            {
                                foreach (DictionaryEntry s1 in ((Hashtable)Servers.old_db[s.Key]))
                                {
                                    b1.AppendFormat("[" + s1.Key.ToString() + "]=" + "'{0}',", r.Cells[s0.Key.ToString()].Value.ToString());
                                }
                            }
                        }
                        sql = (b1.ToString()).Substring(0, b1.ToString().Length - 1);
                        break;
                    #endregion
                }
                return sql;
            }
            catch
            {
                return "";
            }
        }

        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public static bool SelectTable(string p)
        {
            try
            {
                if (DbHelperSQL.Query(string.Format(" select * from sysobjects where [name] = '{0}'", p)).Tables[0].Rows.Count <= 0)
                {
                    return true;
                }
            }
            catch
            { }
            return false;
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="p"></param>
        public static void CreateTable(string p, DataTable dt)
        {
            try
            {
                StringBuilder br1 = new StringBuilder();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    br1.AppendFormat(",{0} nvarchar(200) ", dt.Columns[i].ColumnName);
                }
                StringBuilder br = new StringBuilder();
                br.AppendFormat(" create table {0} ( id int identity(1,1) primary key {1})", p, br1.ToString());
                DbHelperSQL.ExecuteSql(br.ToString());
            }
            catch
            {

            }
        }

        /// <summary>
        /// 测试数据
        /// </summary>
        /// <param name="dtd"></param>
        /// <returns></returns>
        public static object SetCS(DataTable dtd, int count, string strName, string s)
        {
            try
            {
                if (!s.Trim().Equals("无标识列")) dtd.Columns.Remove(s);
                String sql = "insert into {0}({2}) values ({1})";
                StringBuilder br1 = new StringBuilder();
                for (int i = 0; i < dtd.Columns.Count; i++)
                {
                    br1.Append("[" + dtd.Columns[i].ColumnName + "],");
                }
                for (int j = 0; j < count; j++)
                {
                    StringBuilder br = new StringBuilder();
                    for (int i = 0; i < dtd.Columns.Count; i++)
                    {
                            br.Append("'" + new Random().Next(0, 100).ToString() + "',");
                    }
                    sql = String.Format(sql, strName, br.ToString().Substring(0, br.ToString().Length - 1),br1.ToString().Substring(0,br1.ToString().Length-1));
                    DbHelperSQL.ExecuteSql(sql);
                }
                return DbHelperSQL.Query(String.Format(" select * from {0} ", strName)).Tables[0];
            }
            catch
            {
                return null;
            }
        }
    }
}
